from django.shortcuts import render
from django.http import HttpResponse
from .models import *
from django.db.models import *

# def index(request):
    #1,获取所有图书定价的平均值
    # resp = Book.objects.aggregate(Avg('price'))
    # print(resp)
    #
    # print(connection.queries) #打印转换的sql语句
    # 2.获取一共有多少本图书
    # resp = Book.objects.aggregate(book_num=Count('id'))
    # resp = Book.objects.aggregate(book_num=Count('id',distinct=True)) #distinct 去掉重复的默认为False
    # print(resp)  #{'book_num': 5}
    #3.获取图书价格的最大值和最小值
    # resp = Book.objects.aggregate(Max('price'),Min('price')) #{'price__max': 432.0, 'price__min': 43.0}
    # print(resp)


#F表达式:
    #需求:把每个书的价格加10
#不使用F表达式:
    # books = Book.objects.all()
    # for book in books:
    #     book.price +=10
    #     book.save()
#使用F表达式:
    #导入包 F  ps:from django.db.models import F 或者直接* 表示导入所有,其中包括F
    # Book.objects.update(price = F('price')+10)
#Q表达式:
    #1,查询Id= 3的图书
    # Books = Book.objects.filter(Q(id = 3))

    # 2.获取id等于3，或者名字中包含文字"记"的图书
    # Books = Book.objects.filter(Q(id=3)|Q(name = '人'))
    # print(Books)

    # 3.获取价格大于100，并且书名中包含"记"的图书
    # Books = Book.objects.filter(Q(price=100)&Q(name = '人'))
    # print(Books)
    # # 获取书名包含“记”，但是id不等于3的图书
    # Books = Book.objects.filter(Q(id=3) &~Q(name='人'))
    # return HttpResponse('index')

#QuerySet API:
#模型.objects:
    #from django.db.models import Manager其实是一个空壳,它的所有方法都是从`QuerySet`这个类上拷贝过来的.
from django.db.models import Manager
# def index(request):
#     print(type(Book.objects))
#     return HttpResponse('index')

#QuerySet
def index2(request):
#filter:
    #1.查找bookid大于等于2的数据
        # books = Book.objects.filter(id__gte=2)
    # 2.查找bookid大于等于2但是不等于3的数据
        #链式调用
        # books = Book.objects.filter(id__gte=2).filter(~Q(id = 3))
    #3.查询id[1,2,3]的数据
        # books = Book.objects.filter(id__in=[1,2,3])
    #4.查找bookid大于2的数据
        # books = Book.objects.filter(id__gt=2)
    #5.查找bookid小于2的数据
        # books = Book.objects.filter(id__lt=2)
    #6.查找bookid小于等于2的数据
        # books = Book.objects.filter(id__lte=2)

# exclude ：排除满足条件的数据
    #exclude(id = 3)找到id=3,然后排除,实现~Q(id = 3)相同的结果
    # books = Book.objects.filter(id__gte=2).exclude(id = 3)
#annotat:获取每本书名和对应作者名
    # books = Book.objects.annotate(author_name = F('author__name'))
    # for book in books:
    #     print('{}:{}'.format(book.name,book.author_name))
    #     print(books)

        return HttpResponse('index2')

def index3(request):
#order_by(排序):
    #升序
    # order = BookOrder.objects.order_by('price')
    # for i in order:
    #     print('{}:{}'.format(i.id,i.price))
    #倒序
    # order = BookOrder.objects.order_by('-price')
    # for i in order:
    #     print('{}:{}'.format(i.id,i.price))
# 首先根据创建的时间进行排序，如果时间相同，则根据作者的名字进行排序
#     articles = Article.objects.order_by("create_time",'author__name')
    return HttpResponse('index3')